package cn.stylefeng.guns.sys.modular.dict.service.impl;

import cn.stylefeng.guns.sys.modular.dict.entity.SysDictData;
import cn.stylefeng.guns.sys.modular.dict.entity.SysDictType;
import cn.stylefeng.guns.sys.modular.dict.result.AndaDict;
import cn.stylefeng.guns.sys.modular.dict.result.DictDetail;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Sql {

    public static void main(String[] args) {
        String url = "jdbc:mysql://192.168.1.4:3307/saas_test?useAffectedRows=true&autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT&allowMultiQueries=true";
        String password = "root";
        String username = "root";

        Connection connection = null;
        Statement statement = null;
        ResultSet sys_dict_sql = null;
        ResultSet sys_dict_detail_sql = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            String sys_dict = "select * from sys_dict";
            statement = connection.createStatement();
            sys_dict_sql = statement.executeQuery(sys_dict);

            List<SysDictType> andaDictArrayList = new ArrayList<>();
            while (sys_dict_sql.next()){
                SysDictType andaDict = new SysDictType();
                andaDict.setId(sys_dict_sql.getLong("id"));
                andaDict.setCode(sys_dict_sql.getString("name"));
                andaDict.setName(sys_dict_sql.getString("description"));
                andaDict.setSort(1);
                andaDict.setRemark(sys_dict_sql.getString("description"));
                andaDict.setStatus(0);
                andaDict.setTenantId(1l);
                andaDictArrayList.add(andaDict);
            }
           //拼接sql预计

            for(SysDictType sysDictType : andaDictArrayList){
                String sql = "INSERT INTO sys_dict_type ( id, name, code, sort, remark, status, tenement_id )" +
                        " VALUES" +
                        "( " +sysDictType.getId()+",'"
                        +sysDictType.getName()+"','"
                        +sysDictType.getCode()+"',"
                        +sysDictType.getSort() +",'"
                        +sysDictType.getRemark() +"',"
                        +sysDictType.getStatus() +","
                        +sysDictType.getTenantId()
                        +")";
               // statement.executeUpdate(sql);
            }

            //获取dict_type_date
            String sys_dict_detail = "select * from sys_dict_detail";
            sys_dict_detail_sql = statement.executeQuery(sys_dict_detail);
            List<SysDictData> andaDictDataArrayList = new ArrayList<>();
            while (sys_dict_detail_sql.next()){
                SysDictData andaDictData = new SysDictData();

                andaDictData.setTypeId(sys_dict_detail_sql.getLong("dict_id"));
                andaDictData.setValue(sys_dict_detail_sql.getString("label"));
                andaDictData.setCode(sys_dict_detail_sql.getString("value"));
                andaDictData.setSort(sys_dict_detail_sql.getInt("sort"));
                andaDictData.setRemark(sys_dict_detail_sql.getString("label"));
                andaDictData.setStatus(0);
                andaDictDataArrayList.add(andaDictData);
            }

            for(SysDictData sysDictType : andaDictDataArrayList){
                String sql = "INSERT INTO sys_dict_data ( type_id, value, code, sort, remark, status )" +
                        " VALUES" +
                        "( " +sysDictType.getTypeId()+",'"
                        +sysDictType.getValue()+"','"
                        +sysDictType.getCode()+"',"
                        +sysDictType.getSort() +",'"
                        +sysDictType.getRemark() +"',"
                        +sysDictType.getStatus()
                        +")";
                statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
            }


           // connection.commit();

        } catch (SQLException e) {
            e.printStackTrace();
            if(connection==null){
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }finally {
            try {
                if(sys_dict_sql==null){
                    sys_dict_sql.close();
                }
                if(sys_dict_detail_sql==null){
                    sys_dict_sql.close();
                }
                if(statement==null){
                    statement.close();
                }
                if(connection==null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
}
